#!/usr/bin/env python
# -*- coding:utf-8 -*-

"""
JSON Convert Excel(xlsx)
"""

import sys
reload(sys)
sys.setdefaultencoding('utf-8')

import os.path
import ntpath
import optparse
import json
import xlsxwriter

# 获取Excel单元格默认样式
def get_default_format(wb):
    format = wb.add_format()
    format.set_bold(0)
    format.set_text_wrap(0)
    format.set_color('#000000')
    format.set_size(10)
    format.set_border(1)
    format.set_border_color('#000000')
    format.set_align('left')
    format.set_align('vcenter')
    format.set_font('微软雅黑')
    return format

def main(path):
    if not path or not os.path.exists(path) or not os.path.isfile(path):
        raise Exception('Data Path Not Found')
    with open(path, 'r') as fp:
        # Excel地址
        dest_path = '%s.xlsx' % ntpath.splitext(path)[0]
        # 初始化Excel类
        workbook = xlsxwriter.Workbook(dest_path, {'constant_memory': True, 'strings_to_numbers': False, 'strings_to_formulas': False, 'strings_to_urls': False})
        # 初始化参数
        worksheet = None
        column_count = 0
        column = []
        field_list = []
        edit_column = []
        row_index = 3
        row_height = 20
        # 初始化样式
        format_a = get_default_format(workbook)
        format_a.set_align('center')
        format_a.set_text_wrap(0)
        format_a.set_bold(1)
        format_a.set_color('#FF3300')

        format_b = get_default_format(workbook)
        format_b.set_align('center')
        format_b.set_text_wrap(1)
        format_b.set_color('#FFFFFF')
        format_b.set_bold(1)
        format_b.set_bg_color('#6a3427')

        format_c = get_default_format(workbook)

        format_d = get_default_format(workbook)
        format_d.set_bg_color('#EEEEEE')

        format_e = get_default_format(workbook)
        format_e.set_align('left')
        format_e.set_text_wrap(1)
        format_e.set_color('#FFFFFF')
        format_e.set_bold(1)
        format_e.set_bg_color('#6a3427')

        # 处理数据
        for line in fp:
            if not line:
                continue
            arr = json.loads(line, 'utf-8')
            if not arr:
                raise Exception('Json Parser Error {%s}' % line)
            type = arr.get('type')
            data = arr.get('data')
            # 检查数据有效性
            if not data:
                raise Exception('Data Invalid {%s}' % line)
            if type == 'sheet':
                # 创建工作簿
                worksheet = workbook.add_worksheet(data.get('name'))
                # 设置工作簿说明
                for i, v in enumerate(data.get('description')):
                    worksheet.write_string(0, i, v, format_a)
                # 设置行高
                worksheet.set_row(0, 20)
                # 宽度
                row_height = int(data.get('height'))
                column_width = int(data.get('width'))
                # 计算列
                column = []
                edit_column = []
                description_list = []
                example_list = []
                field_list = []
                row_index = 3
                for v in data.get('column'):
                    description_list.append(v.get('description'))
                    example_list.append(v.get('example'))
                    if v.get('edit'):
                        edit_column.append(v.get('field'))
                        column.append('%s[%s]' % (v.get('name'), v.get('field')))
                    else:
                        column.append(v.get('name'))
                    field_list.append(v.get('field'))
                column_count = len(column)
                # 初始化列
                for i, v in enumerate(column):
                    worksheet.write_rich_string(1, i, str(v), format_b)
                # 设置行高
                #worksheet.set_row(1, 20)
                # 设置列宽
                if column_width > 0:
                    worksheet.set_column(0, column_count+1, column_width)
                # 初始化可选值
                for i, v in enumerate(description_list):
                    worksheet.write_rich_string(2, i, str(v), format_e)
                # 设置行高
                worksheet.set_row(2, 80)
                # 初始化参考值
                for i, v in enumerate(example_list):
                    worksheet.write_rich_string(3, i, str(v), format_b)
                # 设置行高
                worksheet.set_row(3, 20)
                # 冻结行
                worksheet.freeze_panes(4, 0)
            elif type == 'row':
                # 判断工作表是否已经初始化
                if not worksheet or column_count <= 0:
                    raise Exception('Excel Sheet Not Initialize')
                # 填充数据
                for i, k in enumerate(field_list):
                    value = data.get(k)
                    if value is None:
                        value = ''
                    if k in edit_column:
                        worksheet.write_rich_string(row_index, i, str(value), format_c)
                    else:
                        worksheet.write_rich_string(row_index, i, str(value), format_d)
                # 设置行高
                if row_height > 0:
                    worksheet.set_row(row_index, row_height)
            else:
                raise Exception('Type Invalid {%s}' % line)
            # 行递增
            row_index += 1
        # 关闭
        workbook.close()
    print 'success {%s}' % dest_path

if __name__ == '__main__':
    parser = optparse.OptionParser()
    parser.add_option('-p', '--path', dest='path', help='Json Data File Path', default=r'')
    try:
        (options, args) = parser.parse_args()
        main(options.path)
    except Exception as e:
        print e.message
